﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using DTO;
using System.Data;

namespace DAO
{
    public class CommentDAO
    {
        #region lay tat ca comment
        public static List<CommentDTO> getAll()
        {
            SqlConnection connect = null;
            List<CommentDTO> list = new List<CommentDTO>();
            try
            {
                connect = DataProvider.Connect();
                string strCommand = "select * from comment";
                SqlCommand command = new SqlCommand(strCommand, connect);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    CommentDTO cm = new CommentDTO();
                    cm.Id = reader.GetInt32(0);
                    cm.TaiKhoan = reader.GetInt32(1);
                    cm.NoiDung = reader.GetString(2);
                    cm.Date = reader.GetString(3);
                    cm.DiaDiem = reader.GetInt32(4);
                    cm.CommentId = reader.GetInt32(5);
                    list.Add(cm);
                }
            }
            catch (Exception ex)
            {
                list = null;
            }
            finally
            {
                if (connect != null && connect.State == System.Data.ConnectionState.Open)
                    connect.Close();
            }
            return list;
        }
        #endregion

        #region lay comment theo ma
        public static List<CommentDTO> getCommentById(int id)
        {
            SqlConnection connect = null;
            List<CommentDTO> list = new List<CommentDTO>();
            try
            {
                connect = DataProvider.Connect();
                string strCommand = "select * from comment where id="+id;
                SqlCommand command = new SqlCommand(strCommand, connect);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    CommentDTO cm = new CommentDTO();
                    cm.Id = reader.GetInt32(0);
                    cm.TaiKhoan = reader.GetInt32(1);
                    cm.NoiDung = reader.GetString(2);
                    cm.Date = reader.GetString(3);
                    cm.DiaDiem = reader.GetInt32(4);
                    cm.CommentId = reader.GetInt32(5);
                    list.Add(cm);
                }
            }
            catch (Exception ex)
            {
                list = null;
            }
            finally
            {
                if (connect != null && connect.State == System.Data.ConnectionState.Open)
                    connect.Close();
            }
            return list;
        }
        #endregion

        #region lấy comment của 1 comment
        public static List<CommentDTO> getCommentofComment(int commentId)
        {
            SqlConnection connect = null;
            List<CommentDTO> list = new List<CommentDTO>();
            try
            {
                connect = DataProvider.Connect();
                string strCommand = "select * from comment where commentid=" + commentId;
                SqlCommand command = new SqlCommand(strCommand, connect);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    CommentDTO cm = new CommentDTO();
                    cm.Id = reader.GetInt32(0);
                    cm.TaiKhoan = reader.GetInt32(1);
                    cm.NoiDung = reader.GetString(2);
                    cm.Date = reader.GetString(3);
                    cm.DiaDiem = reader.GetInt32(4);
                    cm.CommentId = reader.GetInt32(5);
                    list.Add(cm);
                }
            }
            catch (Exception ex)
            {
                list = null;
            }
            finally
            {
                if (connect != null && connect.State == System.Data.ConnectionState.Open)
                    connect.Close();
            }
            return list;
        }
        #endregion

        #region lấy comment của 1 tai khoan
        public static List<CommentDTO> getCommentOfAccount(int taiKhoanID)
        {
            SqlConnection connect = null;
            List<CommentDTO> list = new List<CommentDTO>();
            try
            {
                connect = DataProvider.Connect();
                string strCommand = "select * from comment where taikhoan=" + taiKhoanID;
                SqlCommand command = new SqlCommand(strCommand, connect);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    CommentDTO cm = new CommentDTO();
                    cm.Id = reader.GetInt32(0);
                    cm.TaiKhoan = reader.GetInt32(1);
                    cm.NoiDung = reader.GetString(2);
                    cm.Date = reader.GetString(3);
                    cm.DiaDiem = reader.GetInt32(4);
                    cm.CommentId = reader.GetInt32(5);
                    list.Add(cm);
                }
            }
            catch (Exception ex)
            {
                list = null;
            }
            finally
            {
                if (connect != null && connect.State == System.Data.ConnectionState.Open)
                    connect.Close();
            }
            return list;
        }
        #endregion

        #region lấy comment của 1 dia diem
        public static List<CommentDTO> getCommentOfDiaDiem(int diaDiemId)
        {
            SqlConnection connect = null;
            List<CommentDTO> list = new List<CommentDTO>();
            try
            {
                connect = DataProvider.Connect();
                string strCommand = "select * from comment where diadiem=" + diaDiemId;
                SqlCommand command = new SqlCommand(strCommand, connect);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    CommentDTO cm = new CommentDTO();
                    cm.Id = reader.GetInt32(0);
                    cm.TaiKhoan = reader.GetInt32(1);
                    cm.NoiDung = reader.GetString(2);
                    cm.Date = reader.GetString(3);
                    cm.DiaDiem = reader.GetInt32(4);
                    cm.CommentId = reader.GetInt32(5);
                    list.Add(cm);
                }
            }
            catch (Exception ex)
            {
                list = null;
            }
            finally
            {
                if (connect != null && connect.State == System.Data.ConnectionState.Open)
                    connect.Close();
            }
            return list;
        }
        #endregion

        #region them comment
        public static bool insertComment(CommentDTO cm)
        {
            bool result = true;
            SqlConnection connect = null;
            try
            {
                connect = DataProvider.Connect();
                string strCommand = "insert into comment(taikhoan, noidung, date, diadiem, commentid"
                + " values(@taikhoan, @noidung, @date, @diadiem, @commentid)";
                SqlCommand command = new SqlCommand(strCommand, connect);

                SqlParameter param;
                param = new SqlParameter("@taikhoan", SqlDbType.Int);
                param.Value = cm.TaiKhoan;
                command.Parameters.Add(param);

                param = new SqlParameter("@noidung", SqlDbType.Text);
                param.Value = cm.NoiDung;
                command.Parameters.Add(param);

                param = new SqlParameter("@date", SqlDbType.VarChar);
                param.Value = cm.Date;
                command.Parameters.Add(param);

                param = new SqlParameter("@diadiem", SqlDbType.Int);
                param.Value = cm.DiaDiem;
                command.Parameters.Add(param);

                param = new SqlParameter("@commentid", SqlDbType.Int);
                param.Value = cm.CommentId;
                command.Parameters.Add(param);

                int n = command.ExecuteNonQuery();
                if (n == 0)
                {
                    result = false;
                }
            }
            catch (Exception ex)
            {
                result = false;
            }
            finally
            {
                if (connect != null && connect.State == System.Data.ConnectionState.Open)
                    connect.Close();
            }
            return result;
        }
        #endregion
    }
}
